//    Copyright (C) 2013  <lugocreation@gmail.com>
//
//    This program is free software: you can redistribute it and/or modify
//    it under the terms of the GNU General Public License as published by
//    the Free Software Foundation, either version 3 of the License, or
//    (at your option) any later version.
//
//    This program is distributed in the hope that it will be useful,
//    but WITHOUT ANY WARRANTY; without even the implied warranty of
//    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
//    GNU General Public License for more details.
//
//    You should have received a copy of the GNU General Public License
//    along with this program.  If not, see <http://www.gnu.org/licenses/>.
package org.jpgolay.me.persistence;

import java.io.PrintStream;
import java.sql.*;
import java.util.ArrayList;
import org.jpgolay.me.propertie.PropertyCRUD;
import org.jpgolay.me.util.MEDate;

public class UtilityDB {

    public UtilityDB() {
        prop = new PropertyCRUD();
    }

    public void createDefaultMysqlDB(ArrayList companyList) {
        String meDB = "CREATE DATABASE IF NOT EXISTS " + prop.getDataBaseHome() + ";";
        String meUser = "GRANT ALL PRIVILEGES ON " + prop.getDataBaseHome() + ".* TO " + prop.getUser() + "@'%' IDENTIFIED BY '" + prop.getPass() + "' WITH GRANT OPTION;";
        String meDBService = "CREATE DATABASE IF NOT EXISTS jgservice;";
        String meUserService = "GRANT ALL PRIVILEGES ON jgservice.* TO jgserviceUser@'%' IDENTIFIED BY 'jgservice' WITH GRANT OPTION;";
        String achatsTB = "create table " + prop.getDataBaseHome() + ".achats (id varchar(8) not null primary key, date date, entreprise_id int, locking int, fournisseurs_id varchar(50), commerciaux_id int, no_fact varchar(20), prefixe varchar(20), contenu decimal(10,6), net_a_payer decimal(10,6), total_brut_ht decimal(10,6), total_taxes decimal(10,6), montant_tva1 decimal(10,6), montant_tva2 decimal(10,6), montant_tva3 decimal(10,6), montant_tva4 decimal(10,6), montant_tva5 decimal(10,6), montant_regle decimal(10,6), mode_reglement varchar(20), recu_le date, solde_le date, commentaire varchar(255), update_user_id varchar(50), update_time timestamp, tva_port decimal(10,6));";
        String achatsEnCoursTB = "create table " + prop.getDataBaseHome() + ".achats_en_cours (achats_id varchar(50),articles_id varchar(50));";
        String articlesTB = "create table " + prop.getDataBaseHome() + ".articles (designation varchar(50), familles_articles_id varchar(50), ref_code_barre varchar(50) not null primary key, price_list2 decimal(10,6), price_list3 decimal(10,6), price_list4 decimal(10,6), tva_id int, fournisseurs_id varchar(50),promotion decimal(10,6), emplacement varchar(30), web varchar(30), prix_revient decimal(10,6), price_list1 decimal(10,6), commentaire varchar(255), alerte decimal(10,6), unite_vente varchar(20), update_user_id varchar(50), update_time timestamp, date2 date, date3 date, date4 date, date date, detail_revient varchar(100), free1 varchar(50), free2 varchar(50), prix_achat_pondere decimal(10,6), packaging int, gtin varchar(50), photo1 mediumblob,photo2 mediumblob,photo3 mediumblob,photo4 mediumblob,photo5 mediumblob,photo6 mediumblob,photo7 mediumblob,photo8 mediumblob,photo9 mediumblob,photo10 mediumblob,photos_valid varchar(50),description varchar(250));";
        String detailArticleTB = "create table " + prop.getDataBaseHome() + ".detail_articles (id int not null primary key, detail_list_id int,value varchar(50), notes varchar(250), image mediumblob,update_user_id varchar(50), update_time timestamp, parent_id varchar(50));";
        String detailListTB = "create table " + prop.getDataBaseHome() + ".detail_list (id int not null primary key, name varchar(50), update_user_id varchar(50), update_time timestamp, category varchar(50));";
        String clientsTB = "create table " + prop.getDataBaseHome() + ".clients (id varchar(50) not null primary key, id_gln varchar(50), raison_sociale varchar(50), adresse_fact varchar(100), cp_fact varchar(20), ville_fact varchar(30), pays_fact varchar(30), adresse_livr varchar(100), cp_livr varchar(20), ville_livr varchar(30), pays_livr varchar(30), mode_reglement varchar(20), civilite varchar(20), no_compte varchar(50), commerciaux_id int, contact varchar(50), fonction_contact varchar(50), telephone varchar(20), portable varchar(20), fax varchar(20), email varchar(100), site_web varchar(100), familles_clients_id varchar(50), remise decimal(10,6), remise_excep decimal(10,6), echeance_paiement int, total_encaisse decimal(10,6), cota_fidelite decimal(10,6), commentaire varchar(255), adresse2_fact varchar(100), adresse2_livr varchar(100), banque varchar(50), update_user_id varchar(50), update_time timestamp,photo1 mediumblob,photo2 mediumblob);";
        String detailClientTB = "create table " + prop.getDataBaseHome() + ".detail_clients (id int not null primary key, detail_list_id int,value varchar(50), notes varchar(250), image mediumblob,update_user_id varchar(50), update_time timestamp, parent_id varchar(50));";
        String commParArticleTB = "create table " + prop.getDataBaseHome() + ".comm_par_article (id int not null primary key, commerciaux_id int, articles_id varchar(50), debut date, fin date, t decimal(10,6), update_user_id varchar(50), update_time timestamp);";
        String commParFamilleTB = "create table " + prop.getDataBaseHome() + ".comm_par_famille (id int not null primary key, commerciaux_id int, famille_articles_id varchar(50), debut date, fin date, t decimal(10,6), update_user_id varchar(50), update_time timestamp);";
        String commerciauxTB = "create table " + prop.getDataBaseHome() + ".commerciaux (id int not null primary key, nom varchar(50), prenom varchar(50), addr_ln1 varchar(100), addr_ln2 varchar(100), zip varchar(20), city varchar(50), country varchar(50), secteur varchar(50), telephone varchar(20), portable varchar(20), fax varchar(20), email varchar(100), niveau varchar(50), taux_vente decimal(10,6), taux_total_vente decimal(10,6), resultat decimal(10,6), resultat_date date, objectif decimal(10,6), objectif_date date, commentaire varchar(255), update_user_id varchar(50), update_time timestamp,sales_person_family_id varchar(50));";
        String detailCommerciauxTB = "create table " + prop.getDataBaseHome() + ".detail_commerciaux (id int not null primary key, detail_list_id int,value varchar(50), notes varchar(250), image mediumblob,update_user_id varchar(50), update_time timestamp, parent_id varchar(50));";
        String contenuTB = "create table " + prop.getDataBaseHome() + ".contenu (id int not null primary key, line int, status decimal(10,6), facture_id varchar(50), vente_id varchar(50), article_id varchar(50), designation varchar(250), tva_id int, lot_id varchar(50), reliquat_id int, quantite decimal(10,6), prix_vente decimal(10,6), prix_unitaire decimal(10,6), total_ht decimal(10,6), remise decimal(10,6), update_user_id varchar(50), update_time timestamp, batch_id int);";
        String contenuATB = "create table " + prop.getDataBaseHome() + ".contenu_a (id int not null primary key, line int, status decimal(10,6), facture_id varchar(50), vente_id varchar(50), article_id varchar(50), designation varchar(250), tva_id int, lot_id varchar(50), reliquat_id int, quantite decimal(10,6), prix_vente decimal(10,6), prix_unitaire decimal(10,6), total_ht decimal(10,6), remise decimal(10,6), update_user_id varchar(50), update_time timestamp, batch_id int);";
        String entrepriseTB = "create table " + prop.getDataBaseHome() + ".entreprise (id int not null primary key, raison_sociale varchar(50), civilite varchar(50), adresse varchar(100), cp varchar(20), ville varchar(50), pays varchar(50), telephone varchar(20), fax varchar(20), email varchar(100), siret varchar(50), naf varchar(50), site_web varchar(100), adresse2 varchar(100), telephone_deux varchar(20), telephone_trois varchar(20), fax_deux varchar(20), fax_trois varchar(20), update_user_id varchar(50), update_time timestamp,gln varchar(50), photo1 mediumblob,photo2 mediumblob,notes varchar(250));";
        String detailEntrepriseTB = "create table " + prop.getDataBaseHome() + ".detail_entreprise (id int not null primary key, detail_list_id int,value varchar(50), notes varchar(250), image mediumblob,update_user_id varchar(50), update_time timestamp, parent_id varchar(50));";
        String famillesArticlesTB = "create table " + prop.getDataBaseHome() + ".familles_articles (id varchar(50) not null primary key, designation varchar(50), coefficient decimal(10,6), update_user_id varchar(50), update_time timestamp);";
        String famillesClientsTB = "create table " + prop.getDataBaseHome() + ".familles_clients (id varchar(50) not null primary key, designation varchar(50), coefficient decimal(10,6), update_user_id varchar(50), update_time timestamp);";
        String famillesFournisseursTB = "create table " + prop.getDataBaseHome() + ".familles_fournisseurs (id varchar(50) not null primary key, designation varchar(50), coefficient decimal(10,6), update_user_id varchar(50), update_time timestamp);";
        String salesPersonFamilyTB = "create table " + prop.getDataBaseHome() + ".sales_person_family (id varchar(50) not null primary key, designation varchar(50), coefficient decimal(10,6), update_user_id varchar(50), update_time timestamp);";
        String fournisseursTB = "create table " + prop.getDataBaseHome() + ".fournisseurs (id varchar(50) not null primary key, raison_sociale varchar(50), adresse_com varchar(100), cp_com varchar(20), ville_com varchar(30), pays_com varchar(30), adresse_enlev varchar(100), cp_enlev varchar(20), ville_enlev varchar(30), pays_enlev varchar(30), civilite varchar(20), no_compte varchar(50), contact varchar(50), fonction_contact varchar(50), telephone varchar(20), portable varchar(20), fax varchar(20), email varchar(100), site_web varchar(100), familles_fournisseurs_id varchar(50), commentaire varchar(255), adresse2_com varchar(100), adresse2_enlev varchar(100), update_user_id varchar(50), update_time timestamp,gln varchar(50), photo1 mediumblob,photo2 mediumblob);";
        String detailFournisseurTB = "create table " + prop.getDataBaseHome() + ".detail_fournisseurs (id int not null primary key, detail_list_id int,value varchar(50), notes varchar(250), image mediumblob,update_user_id varchar(50), update_time timestamp, parent_id varchar(50));";
        String journalStockTB = "create table " + prop.getDataBaseHome() + ".journal_stock (id int not null primary key, operation varchar(20), de varchar(50), vers varchar(50), quantite decimal(10,6), date date, update_user_id varchar(50), update_time timestamp, articles_id varchar(50), no_lot int, commentaire varchar(255));";
        String lotsTB = "create table " + prop.getDataBaseHome() + ".batch (id int not null primary key,buy_id varchar(50),sscc varchar(50),trucking_company varchar(50),pallet_gtin varchar(50),box_gtin varchar(50),item_gtin varchar(50),item_id varchar(50),item_batch varchar(50),delivery_form varchar(50),supplier_gln varchar(50),supplier_id varchar(50),agency_id int,input_stock_date date,inventory_date date,dlc date,dluo date,input_stock_quantity decimal(10,6),stock_quantity decimal(10,6),delta_inventory decimal(10,6),packaging decimal(10,6),unit varchar(20),batch_price decimal(10,6),status int,notes varchar(255),update_user_id varchar(50),update_time timestamp);";
        String sendingTB = "create table " + prop.getDataBaseHome() + ".sending (id int not null primary key,order_id varchar(50),sscc varchar(50),trucking_company varchar(50),pallet_gtin varchar(50),box_gtin varchar(50),delivery_form varchar(50),supplier_gln varchar(50),supplier_id varchar(50),agency_id int,sending_date date,preparation_date date,delivery_anticipated_date date,delivery_date date,weight decimal(10,6),parcel_quantity decimal(10,6),packaging decimal(10,6),unit varchar(20),insurance_value decimal(10,6),insurance_company varchar(50),status int,notes varchar(255),update_user_id varchar(50),update_time timestamp);";
        String numeroAchatTB = "create table " + prop.getDataBaseHome() + ".numero_achat (id int not null primary key, time varchar(5));";
        String numeroFactureTB = "create table " + prop.getDataBaseHome() + ".numero_facture (id int not null primary key, time varchar(4));";
        String numeroLotTB = "create table " + prop.getDataBaseHome() + ".numero_lot (id int not null primary key, time varchar(4));";
        String numeroExpeditionTB = "create table " + prop.getDataBaseHome() + ".numero_expedition (id int not null primary key, time varchar(4));";
        String numeroVenteTB = "create table " + prop.getDataBaseHome() + ".numero_vente (id int not null primary key, time varchar(4));";
        String reglementsClientsTB = "create table " + prop.getDataBaseHome() + ".reglements_clients (id int not null primary key, prefix varchar(50), piece_id varchar(10), montant decimal(10,6), mode varchar(30), date date, pointage varchar(30), numero varchar(30), update_user_id varchar(50), update_time timestamp);";
        String remiseFamartParClientTB = "create table " + prop.getDataBaseHome() + ".remise_famart_par_client (id int not null primary key, clients_id varchar(50), familles_articles_id varchar(50), remise decimal(10,6), update_user_id varchar(50), update_time timestamp, qt_un decimal(10,6), qt_deux decimal(10,6), qt_trois decimal(10,6), remise_qt_un decimal(10,6), remise_qt_deux decimal(10,6), remise_qt_trois decimal(10,6));";
        String remiseParClientTB = "create table " + prop.getDataBaseHome() + ".remise_par_client (id int not null primary key, clients_id varchar(50), articles_id varchar(50), remise decimal(10,6), qt_un decimal(10,6), qt_deux decimal(10,6), qt_trois decimal(10,6), remise_qt_un decimal(10,6), remise_qt_deux decimal(10,6), remise_qt_trois decimal(10,6), update_user_id varchar(50), update_time timestamp);";
        String dbTB = "create table jgservice.db (id int not null primary key, name varchar(50), dbname varchar(30), login varchar(50), password varchar(50), url varchar(100), admin_login varchar(50), admin_password varchar(50), admin_url varchar(100), notes varchar(100), update_user_id varchar(50), update_time timestamp, date date);";
        String reminderTB = "create table " + prop.getDataBaseHome() + ".reminder (id int not null primary key, piece_id varchar(50), date date, status int, notes varchar(255), update_user_id varchar(50), update_time timestamp);";
        String tvaTB = "create table " + prop.getDataBaseHome() + ".tva (id int primary key, taux decimal(10,6), update_user_id varchar(50), update_time timestamp);";
        String utilisateursTB = "create table " + prop.getDataBaseHome() + ".utilisateurs (id varchar(50) not null primary key, password varchar(50), nom varchar(50), prenom varchar(50), droits int, groups varchar(50), adrln1 varchar(100), adrln2 varchar(100), ville varchar(50), cp varchar(20), tel1 varchar(20), tel2 varchar(20), fax varchar(20), email varchar(100), job varchar(50), notes varchar(250), msg varchar(250), nb_connection int, expiry_date date, update_user_id varchar(50), update_time timestamp, photo1 mediumblob, photo2 mediumblob );";
        String detailUtilisateursTB = "create table " + prop.getDataBaseHome() + ".detail_utilisateurs (id int not null primary key, detail_list_id int,value varchar(50), notes varchar(250), image mediumblob,update_user_id varchar(50), update_time timestamp, parent_id varchar(50));";
        String ventesTB = "create table " + prop.getDataBaseHome() + ".ventes (id varchar(8) not null primary key, prefixe varchar(20), date date, clients_id varchar(50), commerciaux_id int, contenu decimal(10,6), net_a_payer decimal(10,6), echeance date, date_livraison date, livre varchar(100), date_relance date, montant_regle decimal(10,6), mode_reglement varchar(20), solde_le date, entreprise_id int, prepared int, total_brut_ht decimal(10,6), commentaire varchar(255), total_taxes decimal(10,6), montant_tva1 decimal(10,6), montant_tva2 decimal(10,6), montant_tva3 decimal(10,6), montant_tva4 decimal(10,6), montant_tva5 decimal(10,6), no_fact varchar(10), update_user_id varchar(50), update_time timestamp, total_sous_total decimal(10,6), tva_port decimal(10,6));";
        String ventesFacturesTB = "create table " + prop.getDataBaseHome() + ".ventes_factures (id varchar(8) not null primary key, prefixe varchar(20), date date, clients_id varchar(50), commerciaux_id int, contenu decimal(10,6), net_a_payer decimal(10,6), echeance date, date_relance date, montant_regle decimal(10,6), mode_reglement varchar(20), solde_le date, entreprise_id int, locking int, total_brut_ht decimal(10,6), commentaire varchar(255), total_taxes decimal(10,6), montant_tva1 decimal(10,6), montant_tva2 decimal(10,6), montant_tva3 decimal(10,6), montant_tva4 decimal(10,6), montant_tva5 decimal(10,6), total_sous_total decimal(10,6), ventes varchar(100), update_user_id varchar(50), update_time timestamp, tva_port decimal(10,6));";
        String advanceSearchTB = "create table " + prop.getDataBaseHome() + ".advance_search (id int not null primary key, name varchar(50), sql_request varchar(250), update_user_id varchar(50), update_time timestamp, category varchar(50));";
        String defaultValuesUtilisateur = "insert into " + prop.getDataBaseHome() + ".utilisateurs (id,password,nom,prenom,droits) values ('jgestcom','admin','Administrateur','JGestcom',1073741823);";
        String defaultValuesDb = "insert into jgservice.db (id,name,dbname,login,password,url,admin_login,admin_password,admin_url,notes,date,update_user_id) values (1,'Default DB','" + prop.getDataBaseHome() + "','" + prop.getUser() + "','" + prop.getPass() + "','" + prop.getUrl() + "','" + prop.getFirstUser() + "','" + prop.getFirstPass() + "','" + prop.getFirstUrl() + "','DB create by default','" + (new MEDate()).getMysql() + "','jgestcom');";
        String defaultValuesTva1 = "insert into " + prop.getDataBaseHome() + ".tva (id,taux,update_user_id,update_time) values (1,19.6,'jgestcom','2013-01-01 00:00:00');";
        String defaultValuesTva2 = "insert into " + prop.getDataBaseHome() + ".tva (id,taux,update_user_id,update_time) values (2,5.5,'jgestcom','2013-01-01 00:00:00');";
        String defaultValuesTva3 = "insert into " + prop.getDataBaseHome() + ".tva (id,taux,update_user_id,update_time) values (3,0,'jgestcom','2013-01-01 00:00:00');";
        String defaultValuesTva4 = "insert into " + prop.getDataBaseHome() + ".tva (id,taux,update_user_id,update_time) values (4,0,'jgestcom','2013-01-01 00:00:00');";
        String defaultValuesTva5 = "insert into " + prop.getDataBaseHome() + ".tva (id,taux,update_user_id,update_time) values (5,0,'jgestcom','2013-01-01 00:00:00');";
        String defaultValuesNumeroAchat = "insert into " + prop.getDataBaseHome() + ".numero_achat (id,time) values (1,'f0101');";
        String defaultValuesNumeroFacture = "insert into " + prop.getDataBaseHome() + ".numero_facture (id,time) values (1,'0101');";
        String defaultValuesNumeroLots = "insert into " + prop.getDataBaseHome() + ".numero_lot (id,time) values (1,'0101');";
        String defaultValuesNumeroExpeditions = "insert into " + prop.getDataBaseHome() + ".numero_expedition (id,time) values (1,'0101');";
        String defaultValuesNumeroVente = "insert into " + prop.getDataBaseHome() + ".numero_vente (id,time) values (1,'0101');";
        String defaultValuesFamilleArticle1 = "insert into " + prop.getDataBaseHome() + ".familles_articles (id,designation,coefficient,update_user_id,update_time) values ('UC','Unit\351 central (defaut)',0,'jgestcom','2004-01-01 00:00:00');";
        String defaultValuesFamilleArticle2 = "insert into " + prop.getDataBaseHome() + ".familles_articles (id,designation,coefficient,update_user_id,update_time) values ('CONSOMMABLE','Consommabble (defaut)',0,'jgestcom','2004-01-01 00:00:00');";
        String defaultValuesFamilleArticle3 = "insert into " + prop.getDataBaseHome() + ".familles_articles (id,designation,coefficient,update_user_id,update_time) values ('LOGICIEL','Logiciel (defaut)',0,'jgestcom','2004-01-01 00:00:00');";
        String defaultValuesFamilleArticle4 = "insert into " + prop.getDataBaseHome() + ".familles_articles (id,designation,coefficient,update_user_id,update_time) values ('PERIPHERIQUE','P\351riph\351rique (defaut)',0,'jgestcom','2004-01-01 00:00:00');";
        String defaultValuesFamilleClient1 = "insert into " + prop.getDataBaseHome() + ".familles_clients (id,designation,coefficient,update_user_id,update_time) values ('PROFESSIONNEL','Professionnel (defaut)',0,'jgestcom','2004-01-01 00:00:00');";
        String defaultValuesFamilleClient2 = "insert into " + prop.getDataBaseHome() + ".familles_clients (id,designation,coefficient,update_user_id,update_time) values ('PARTICULIER','Particulier (defaut)',0,'jgestcom','2004-01-01 00:00:00');";
        String defaultValuesFamilleFournisseur1 = "insert into " + prop.getDataBaseHome() + ".familles_fournisseurs (id,designation,coefficient,update_user_id,update_time) values ('GROSSISTE','Grossiste (defaut)',0,'jgestcom','2004-01-01 00:00:00');";
        String defaultValuesFamilleFournisseur2 = "insert into " + prop.getDataBaseHome() + ".familles_fournisseurs (id,designation,coefficient,update_user_id,update_time) values ('FABRICANT','Fabricant (defaut)',0,'jgestcom','2004-01-01 00:00:00');";
        String defaultValuesSalesPersonFamily1 = "insert into " + prop.getDataBaseHome() + ".sales_person_family (id,designation,coefficient,update_user_id,update_time) values ('CLIENTS','Clients (defaut)',0,'jgestcom','2004-01-01 00:00:00');";
        String defaultValuesSalesPersonFamily2 = "insert into " + prop.getDataBaseHome() + ".sales_person_family (id,designation,coefficient,update_user_id,update_time) values ('FOURNISSEURS','Fournisseurs (defaut)',0,'jgestcom','2004-01-01 00:00:00');";
        String defaultValuesEntreprise1 = "insert into " + prop.getDataBaseHome() + ".entreprise (id,raison_sociale,gln,naf,siret,email,fax,telephone,pays,civilite,adresse,adresse2,ville,cp,update_user_id,update_time) values (1,'DEMO JGestCom','12563256','123','0123456789','infos@jgestcom.com','0011223344','0022113344','France','SARL','Place du March\351 46','Immeuble le Mill\351naire','Montpellier','34000','jgestcom','2004-01-01 00:00:00');";
        String defaultValuesEntreprise2 = "insert into " + prop.getDataBaseHome() + ".entreprise (id,raison_sociale,update_user_id,update_time) values (2,'Agence 2','jgestcom','2004-01-01 00:00:00');";
        String defaultValuesEntreprise3 = "insert into " + prop.getDataBaseHome() + ".entreprise (id,raison_sociale,update_user_id,update_time) values (3,'Agence 3','jgestcom','2004-01-01 00:00:00');";
        String defaultValuesEntreprise4 = "insert into " + prop.getDataBaseHome() + ".entreprise (id,raison_sociale,update_user_id,update_time) values (4,'Agence 4','jgestcom','2004-01-01 00:00:00');";
        String defaultValuesEntreprise5 = "insert into " + prop.getDataBaseHome() + ".entreprise (id,raison_sociale,update_user_id,update_time) values (5,'Agence 5','jgestcom','2004-01-01 00:00:00');";
        String defaultValuesCommerciaux1 = "insert into " + prop.getDataBaseHome() + ".commerciaux (id,nom,prenom,commentaire,taux_vente,taux_total_vente,update_user_id,update_time,sales_person_family_id) values (1,'White','John','Commercial par defaut 1',5,2,'jgestcom','2004-01-01 00:00:00','CLIENTS');";
        String defaultValuesCommerciaux2 = "insert into " + prop.getDataBaseHome() + ".commerciaux (id,nom,prenom,commentaire,taux_vente,taux_total_vente,update_user_id,update_time,sales_person_family_id) values (2,'Smith','Brian','Commercial par defaut 2',4,1,'jgestcom','2004-01-01 00:00:00','FOURNISSEURS');";
        String defaultValuesFournisseur1 = "insert into " + prop.getDataBaseHome() + ".fournisseurs (id,gln,fax,telephone,fonction_contact,contact,pays_com,cp_com,ville_com,adresse_com,civilite,raison_sociale,familles_fournisseurs_id,commentaire,update_user_id,update_time,adresse_enlev,cp_enlev,ville_enlev,pays_enlev,no_compte,portable,email,site_web,adresse2_com,adresse2_enlev) values ('IOL','1256325','0033667788','0044885566','Directeur commercial','Mme Franel','France','38000','Grenoble','Rue de la Gare 146','SA','Info-on-line (defaut)','GROSSISTE','Fournisseur par defaut1','jgestcom','2004-01-01 00:00:00','','','','','','','','','','');";
        String defaultValuesFournisseur2 = "insert into " + prop.getDataBaseHome() + ".fournisseurs (id,gln,fax,telephone,fonction_contact,contact,pays_com,cp_com,ville_com,adresse_com,civilite,raison_sociale,familles_fournisseurs_id,commentaire,update_user_id,update_time,adresse_enlev,cp_enlev,ville_enlev,pays_enlev,no_compte,portable,email,site_web,adresse2_com,adresse2_enlev) values ('DECC','1256398','0033998855','0055669944','Responsable sud ouest','M Grangier','France','31000','Toulouse','Place des fontaines 21','SA','Dec computer (defaut)','FABRICANT','Fournisseur par defaut2','jgestcom','2004-01-01 00:00:00','','','','','','','','','','');";
        String defaultValuesClient1 = "insert into " + prop.getDataBaseHome() + ".clients (id,fax,telephone,fonction_contact,contact,civilite,pays_fact,cp_fact,ville_fact,adresse_fact,id_gln,raison_sociale,commerciaux_id,familles_clients_id,remise,remise_excep,echeance_paiement,commentaire,update_user_id,update_time,adresse_livr,cp_livr,ville_livr,pays_livr,mode_reglement,no_compte,portable,email,site_web,total_encaisse,cota_fidelite,adresse2_fact,adresse2_livr,banque) values ('FAC-INF','0033665588','0033889944','Responsable achats','M Durant','SARL','France','69000','Lyon','Avenue de la R\351publique 78','12653','Fac Informatique (par defaut)',1,'PROFESSIONNEL',2,0,10,'Fac Informatique (par defaut)','jgestcom','2004-01-01 00:00:00','','','','','','','','','',0.0,0.0,'','','');";
        String defaultValuesClient2 = "insert into " + prop.getDataBaseHome() + ".clients (id,fax,telephone,fonction_contact,contact,civilite,pays_fact,cp_fact,ville_fact,adresse_fact,id_gln,raison_sociale,commerciaux_id,familles_clients_id,remise,remise_excep,echeance_paiement,commentaire,update_user_id,update_time,adresse_livr,cp_livr,ville_livr,pays_livr,mode_reglement,no_compte,portable,email,site_web,total_encaisse,cota_fidelite,adresse2_fact,adresse2_livr,banque) values ('TOP-PC','0088226655','0088447766','Gerant','M Pierre','SARL','France','06000','Nice','Rue du port 15','26935','PC TOP (par defaut)',1,'PROFESSIONNEL',0,0,10,'PC TOP (par defaut)','jgestcom','2004-01-01 00:00:00','','','','','','','','','',0.0,0.0,'','','');";
        String defaultValuesClient3 = "insert into " + prop.getDataBaseHome() + ".clients (id,fax,telephone,fonction_contact,contact,civilite,pays_fact,cp_fact,ville_fact,adresse_fact,id_gln,raison_sociale,commerciaux_id,familles_clients_id,remise,remise_excep,echeance_paiement,commentaire,update_user_id,update_time,adresse_livr,cp_livr,ville_livr,pays_livr,mode_reglement,no_compte,portable,email,site_web,total_encaisse,cota_fidelite,adresse2_fact,adresse2_livr,banque) values ('RAMIR','0099556633','0055889977','Comptable','Mem Pranelle','SARL','France','13000','Marseille','Boulvard Henri4 145bis','68954','M Ramirez Jacques',1,'PARTICULIER',0,0,10,'Client (par defaut)','jgestcom','2004-01-01 00:00:00','','','','','','','','','',0.0,0.0,'','','');";
        String defaultValuesArticle1 = "insert into " + prop.getDataBaseHome() + ".articles (ref_code_barre,designation,familles_articles_id,price_list2,tva_id,fournisseurs_id,promotion,prix_revient,price_list1,commentaire,update_user_id,update_time,price_list3,price_list4,emplacement,web,alerte,unite_vente,date2,date3,date4,date,detail_revient,prix_achat_pondere,packaging,gtin,photos_valid,description) values ('UC-45','Unit\351 central','UC',480.0,1,'DEF_FOURN1',0,525.0,890.0,'Article par defaut1','jgestcom','2004-01-01 00:00:00',480.0,480.0,'Depot B','non',10.00,'pi\350ce','2004-02-10','2004-02-10','2004-02-10','2004-02-10','ports',480.0,1,'0123456','non','description...');";
        String defaultValuesArticle2 = "insert into " + prop.getDataBaseHome() + ".articles (ref_code_barre,designation,familles_articles_id,price_list2,tva_id,fournisseurs_id,promotion,prix_revient,price_list1,commentaire,update_user_id,update_time,price_list3,price_list4,emplacement,web,alerte,unite_vente,date2,date3,date4,date,detail_revient,prix_achat_pondere,packaging,gtin,photos_valid,description) values ('CL-21','Clavier','PERIPHERIQUE',14.0,1,'DEF_FOURN1',0,15.0,65.0,'Article par defaut2','jgestcom','2004-01-01 00:00:00',14.0,14.0,'Depot B','non',10.00,'pi\350ce','2004-02-10','2004-02-10','2004-02-10','2004-02-10','ports',14.0,1,'0123456','non','description...');";
        String defaultValuesArticle3 = "insert into " + prop.getDataBaseHome() + ".articles (ref_code_barre,designation,familles_articles_id,price_list2,tva_id,fournisseurs_id,promotion,prix_revient,price_list1,commentaire,update_user_id,update_time,price_list3,price_list4,emplacement,web,alerte,unite_vente,date2,date3,date4,date,detail_revient,prix_achat_pondere,packaging,gtin,photos_valid,description) values ('SR-68','Souris','PERIPHERIQUE',10.0,1,'DEF_FOURN2',0,12.0,34.0,'Article par defaut3','jgestcom','2004-01-01 00:00:00',10.0,10.0,'Depot B','non',10.00,'pi\350ce','2004-02-10','2004-02-10','2004-02-10','2004-02-10','ports',10.0,1,'0123456','non','description...');";
        String defaultValuesArticle4 = "insert into " + prop.getDataBaseHome() + ".articles (ref_code_barre,designation,familles_articles_id,price_list2,tva_id,fournisseurs_id,promotion,prix_revient,price_list1,commentaire,update_user_id,update_time,price_list3,price_list4,emplacement,web,alerte,unite_vente,date2,date3,date4,date,detail_revient,prix_achat_pondere,packaging,gtin,photos_valid,description) values ('EC-121','Ecran TFT21','PERIPHERIQUE',395.0,1,'DEF_FOURN2',0,395.0,795.0,'Article par defaut4','jgestcom','2004-01-01 00:00:00',395.0,395.0,'Depot B','non',10.00,'pi\350ce','2004-02-10','2004-02-10','2004-02-10','2004-02-10','ports',395.0,1,'0123456','non','description...');";
        String defaultValuesArticle5 = "insert into " + prop.getDataBaseHome() + ".articles (ref_code_barre,designation,familles_articles_id,price_list2,tva_id,fournisseurs_id,promotion,prix_revient,price_list1,commentaire,update_user_id,update_time,price_list3,price_list4,emplacement,web,alerte,unite_vente,date2,date3,date4,date,detail_revient,prix_achat_pondere,packaging,gtin,photos_valid,description) values ('EC-115','Ecran TFT15','PERIPHERIQUE',218.0,1,'DEF_FOURN1',0,218.0,468.0,'Article par defaut5','jgestcom','2004-01-01 00:00:00',218.0,218.0,'Depot B','non',10.00,'pi\350ce','2004-02-10','2004-02-10','2004-02-10','2004-02-10','ports',218.0,1,'0123456','non','description...');";
        String defaultValuesArticle6 = "insert into " + prop.getDataBaseHome() + ".articles (ref_code_barre,designation,familles_articles_id,price_list2,tva_id,fournisseurs_id,promotion,prix_revient,price_list1,commentaire,update_user_id,update_time,price_list3,price_list4,emplacement,web,alerte,unite_vente,date2,date3,date4,date,detail_revient,prix_achat_pondere,packaging,gtin,photos_valid,description) values ('IM-72','Imprimante','PERIPHERIQUE',100.0,1,'DEF_FOURN1',0,100.0,254.0,'Article par defaut6','jgestcom','2004-01-01 00:00:00',100.0,100.0,'Depot B','non',10.00,'pi\350ce','2004-02-10','2004-02-10','2004-02-10','2004-02-10','ports',100.0,1,'0123456','non','description...');";
        String defaultValuesArticle7 = "insert into " + prop.getDataBaseHome() + ".articles (ref_code_barre,designation,familles_articles_id,price_list2,tva_id,fournisseurs_id,promotion,prix_revient,price_list1,commentaire,update_user_id,update_time,price_list3,price_list4,emplacement,web,alerte,unite_vente,date2,date3,date4,date,detail_revient,prix_achat_pondere,packaging,gtin,photos_valid,description) values ('SC-49','Scanner','PERIPHERIQUE',60.0,1,'DEF_FOURN1',0,62.0,129.0,'Article par defaut7','jgestcom','2004-01-01 00:00:00',60.0,60.0,'Depot B','non',10.00,'pi\350ce','2004-02-10','2004-02-10','2004-02-10','2004-02-10','ports',60.0,1,'0123456','non','description...');";
        String defaultValuesArticle8 = "insert into " + prop.getDataBaseHome() + ".articles (ref_code_barre,designation,familles_articles_id,price_list2,tva_id,fournisseurs_id,promotion,prix_revient,price_list1,commentaire,update_user_id,update_time,price_list3,price_list4,emplacement,web,alerte,unite_vente,date2,date3,date4,date,detail_revient,prix_achat_pondere,packaging,gtin,photos_valid,description) values ('OS-WD2','OS Wd','LOGICIEL',180.0,1,'DEF_FOURN2',0,180.0,389.0,'Article par defaut8','jgestcom','2004-01-01 00:00:00',180.0,180.0,'Depot B','non',10.00,'pi\350ce','2004-02-10','2004-02-10','2004-02-10','2004-02-10','ports',180.0,1,'0123456','non','description...');";
        String defaultValuesArticle9 = "insert into " + prop.getDataBaseHome() + ".articles (ref_code_barre,designation,familles_articles_id,price_list2,tva_id,fournisseurs_id,promotion,prix_revient,price_list1,commentaire,update_user_id,update_time,price_list3,price_list4,emplacement,web,alerte,unite_vente,date2,date3,date4,date,detail_revient,prix_achat_pondere,packaging,gtin,photos_valid,description) values ('OS-LX1','OS Linux','LOGICIEL',5.0,1,'DEF_FOURN1',0,5.0,10.0,'Article par defaut9','jgestcom','2004-01-01 00:00:00',5.0,5.0,'Depot B','non',10.00,'pi\350ce','2004-02-10','2004-02-10','2004-02-10','2004-02-10','ports',5.0,1,'0123456','non','description...');";
        String defaultValuesArticle10 = "insert into " + prop.getDataBaseHome() + ".articles (ref_code_barre,designation,familles_articles_id,price_list2,tva_id,fournisseurs_id,promotion,prix_revient,price_list1,commentaire,update_user_id,update_time,price_list3,price_list4,emplacement,web,alerte,unite_vente,date2,date3,date4,date,detail_revient,prix_achat_pondere,packaging,gtin,photos_valid,description) values ('LO-PK44','Pack Suite Logiciel','LOGICIEL',290.0,1,'DEF_FOURN2',0,290.0,525.0,'Article par defaut10','jgestcom','2004-01-01 00:00:00',290.0,290.0,'Depot B','non',10.00,'pi\350ce','2004-02-10','2004-02-10','2004-02-10','2004-02-10','ports',290.0,1,'0123456','non','description...');";
        try {
            initDB();
            firstConnectDB();
            st = con.createStatement();
            st.execute(meDB);
            st.execute(meDBService);
            st.execute(meUser);
            st.execute(meUserService);
            st.close();
            con.close();
            connectDB();
            st = con.createStatement();
            st.execute(achatsTB);
            st.execute(achatsEnCoursTB);
            st.execute(articlesTB);
            st.execute(detailArticleTB);
            st.execute(detailListTB);
            st.execute(clientsTB);
            st.execute(detailClientTB);
            st.execute(commParArticleTB);
            st.execute(commParFamilleTB);
            st.execute(commerciauxTB);
            st.execute(detailCommerciauxTB);
            st.execute(contenuTB);
            st.execute(contenuATB);
            st.execute(entrepriseTB);
            st.execute(detailEntrepriseTB);
            st.execute(famillesArticlesTB);
            st.execute(famillesClientsTB);
            st.execute(famillesFournisseursTB);
            st.execute(salesPersonFamilyTB);
            st.execute(fournisseursTB);
            st.execute(detailFournisseurTB);
            st.execute(journalStockTB);
            st.execute(lotsTB);
            st.execute(sendingTB);
            st.execute(numeroAchatTB);
            st.execute(numeroFactureTB);
            st.execute(numeroLotTB);
            st.execute(numeroExpeditionTB);
            st.execute(numeroVenteTB);
            st.execute(reglementsClientsTB);
            st.execute(remiseFamartParClientTB);
            st.execute(remiseParClientTB);
            st.execute(reminderTB);
            st.execute(tvaTB);
            st.execute(utilisateursTB);
            st.execute(detailUtilisateursTB);
            st.execute(ventesTB);
            st.execute(ventesFacturesTB);
            st.execute(advanceSearchTB);
            st.execute(defaultValuesUtilisateur);
            st.execute(defaultValuesTva1);
            st.execute(defaultValuesTva2);
            st.execute(defaultValuesTva3);
            st.execute(defaultValuesTva4);
            st.execute(defaultValuesTva5);
            st.execute(defaultValuesNumeroAchat);
            st.execute(defaultValuesNumeroFacture);
            st.execute(defaultValuesNumeroLots);
            st.execute(defaultValuesNumeroExpeditions);
            st.execute(defaultValuesNumeroVente);
            st.execute(defaultValuesFamilleArticle1);
            st.execute(defaultValuesFamilleArticle2);
            st.execute(defaultValuesFamilleArticle3);
            st.execute(defaultValuesFamilleArticle4);
            st.execute(defaultValuesFamilleClient1);
            st.execute(defaultValuesFamilleClient2);
            st.execute(defaultValuesFamilleFournisseur1);
            st.execute(defaultValuesFamilleFournisseur2);
            st.execute(defaultValuesSalesPersonFamily1);
            st.execute(defaultValuesSalesPersonFamily2);
            st.execute(defaultValuesEntreprise1);
            st.execute(defaultValuesEntreprise2);
            st.execute(defaultValuesEntreprise3);
            st.execute(defaultValuesEntreprise4);
            st.execute(defaultValuesEntreprise5);
            st.execute(defaultValuesCommerciaux1);
            st.execute(defaultValuesCommerciaux2);
            st.execute(defaultValuesFournisseur1);
            st.execute(defaultValuesFournisseur2);
            st.execute(defaultValuesClient1);
            st.execute(defaultValuesClient2);
            st.execute(defaultValuesClient3);
            st.execute(defaultValuesArticle1);
            st.execute(defaultValuesArticle2);
            st.execute(defaultValuesArticle3);
            st.execute(defaultValuesArticle4);
            st.execute(defaultValuesArticle5);
            st.execute(defaultValuesArticle6);
            st.execute(defaultValuesArticle7);
            st.execute(defaultValuesArticle8);
            st.execute(defaultValuesArticle9);
            st.execute(defaultValuesArticle10);
            st.close();
            con.close();
            connectDBService();
            st = con.createStatement();
            st.execute(dbTB);
            if (companyList != null) {
                st.execute(defaultValuesDb);
            } else {
                st.execute(defaultValuesDb);
            }
        } catch (SQLException sqlexception) {
        } finally {
            try {
                if (st != null) {
                    st.close();
                }
                if (con != null) {
                    con.close();
                }
            } catch (SQLException e1) {
                e1.printStackTrace();
            }
        }
        return;
    }

    public void deleteDefaultMysqlDB() {
        try {
            initDB();
            firstConnectDB();
            st = con.createStatement();
            st.execute("drop database " + prop.getDataBaseHome());
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (st != null) {
                    st.close();
                }
                if (con != null) {
                    con.close();
                }
            } catch (SQLException e1) {
                e1.printStackTrace();
            }
        }
        return;
    }

    private void firstConnectDB() {
        try {
            con = DriverManager.getConnection(prop.getFirstUrl(), prop.getFirstUser(), prop.getFirstPass());
        } catch (SQLException sqlex) {
            System.out.println("Impossible d'ouvrir une (firstConnectDB) connection sur la base de donn\351e !");
            System.out.println("\t\turl : " + prop.getFirstUrl());
            System.out.println("\t   user : " + prop.getFirstUser());
            System.out.println("\t   pass : " + prop.getFirstPass());
            System.out.println(sqlex);
        }
    }

    private void connectDB() {
        try {
            con = DriverManager.getConnection(prop.getUrl(), prop.getUser(), prop.getPass());
        } catch (SQLException sqlex) {
            System.out.println("Impossible d'ouvrir une (connectDB) connection sur la base de donn\351e !");
            System.out.println("\t\turl : " + prop.getUrl());
            System.out.println("\t   user : " + prop.getUser());
            System.out.println("\t   pass : " + prop.getPass());
            System.out.println(sqlex);
        }
    }

    private void connectDBService() {
        try {
            con = DriverManager.getConnection(prop.getUrl().substring(0, prop.getUrl().lastIndexOf("/")) + "/jgservice", "jgserviceUser", "jgservice");
        } catch (SQLException sqlex) {
            System.out.println("Impossible d'ouvrir une (connectDBService) connection sur la base de donn\351e !");
            System.out.println("\t\turl : " + prop.getUrl().substring(0, prop.getUrl().lastIndexOf("/")) + "/jgservice");
            System.out.println("\t    user : jgserviceUser");
            System.out.println("\t    pass : jgservice");
            System.out.println(sqlex);
        }
    }

    private void initDB() {
        try {
            Class.forName(prop.getDriver());
        } catch (ClassNotFoundException cnfex) {
            System.out.println("Impossible de charger le driver JDBC !\ndriver : " + prop.getDriver());
            System.out.println(cnfex);
        }
    }

    public static final void main(String args[]) {
        new UtilityDB();
    }
    private PropertyCRUD prop;
    private Connection con;
    private Statement st;
}
